package com.ftg.learn.bus.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ftg.learn.bus.VO.UserRoleNameVo;
import com.ftg.learn.bus.VO.busExCostAmountVo;
import com.ftg.learn.bus.entity.BusExpenditure;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ftg.learn.bus.entity.Fund;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 公车经费支出登记表 Mapper 接口
 * </p>
 *
 * @author lalala
 * @since 2022-03-07
 */
public interface BusExpenditureMapper extends BaseMapper<BusExpenditure> {

    /**
     * 通过公车使用登记表id查询出 相应的公车信息数据。
     * @param burid
     * @return
     */
    @Select("select a.burid,b.bus_no,c.dname,b.ins_equ_no,b.receiver from " +
            "sys_bus_use_registration as a left join sys_bus_info as b on " +
            "a.bid = b.bid left join sys_director as c on b.did = c.did where " +
            "a.burid = #{param1}")
    Map<String,Object> selectAutoInfo(Long burid);

    /**
     * 根据burid查询此登记表内的审核状态
     * @param burid
     * @return
     */
    @Select("select is_special from sys_bus_use_registration where burid = #{param1}")
    String selectExStatus(Long burid);

    /**
     * 根据参数表pId查询 此参数的名称。
     * @param pid
     * @return
     */
    @Select("select pname from sys_parameter where pid = #{param1}")
    String selectPname(Long pid);

    /**
     * 分页查询经费支出表全部数据加上对应beid的经费表经费支出总和。
     * @param pagination
     * @return
     */
    @Select("SELECT a.*,sum(ifnull(b.cost_amount,0)) FROM " +
            "sys_bus_expenditure as a left JOIN sys_fund as b " +
            "on a.beid = b.beid GROUP BY a.beid")
    List<busExCostAmountVo> showBusExAndSumCost(IPage<busExCostAmountVo> pagination);

    /**
     * 按负责人姓名模糊查找页查询经费支出表全部数据加上对应beid的经费表经费支出总和。
     * @return
     */
    @Select("SELECT a.*,sum(ifnull(b.cost_amount,0)) FROM " +
            "sys_bus_expenditure as a left JOIN sys_fund as b " +
            "on a.beid = b.beid where a.dname like CONCAT('%',#{dname},'%') GROUP BY a.beid ")
    List<busExCostAmountVo> showBusExAndSumCostByDname(IPage<busExCostAmountVo> pagination,@Param("dname") String dname);


    /**
     * 在审批表内查询经费支出表全部数据加上对应beid的经费表经费支出总和。
     * @return
     */
    @Select("SELECT a.*,sum(ifnull(b.cost_amount,0)) FROM " +
            "sys_bus_expenditure as a left JOIN sys_fund as b " +
            "on a.beid = b.beid where a.beid = #{param1} GROUP BY a.beid ")
    List<busExCostAmountVo> showBusExAndSumCostInApp(Long beid);


    /**
     * 根据此经费支出表id查询此次支出表详情。
     * @param beid
     * @return
     */
    @Select("select * from " +
            "sys_fund where beid = #{beid}")
    List<Fund> showFund(@Param("beid") Long beid);

    /**
     * 列出参数表中所有的经费类型
     * @return
     */
    @Select("select pid,pname from sys_parameter where parentId = 1")
    List<Map<String,Object>> showCostType();


    /**
     * 列出参数表中所有的厂商。
     * @return
     */
    @Select("select pname from sys_parameter where parentId in (2,3,4)")
    List<Map<String,Object>> showManufacturer();


    /**
     * 向经费表Fund中添加数据。
     * @param beid
     * @param cost_type
     * @param cost_amount
     * @param pid
     * @param invoice_no
     * @return
     */
    @Insert("insert into sys_fund (beid,pid,cost_type,cost_amount,invoice_no,manufacturer) values(" +
            "#{param1},#{param2},#{param3},#{param4},#{param5},#{param6})")
    boolean insertFund(Long beid,Long pid,String cost_type,Double cost_amount,String invoice_no,String manufacturer);


    /**
     * 根据beid删除fund表内的数据
     * @param beid
     * @return
     */
    @Delete("delete from sys_fund where beid = #{param1}")
    boolean deleteFund(Long beid);


    /**
     * 根据beid删除审批表内的数据
     * @param beid
     * @return
     */
    @Delete("delete from sys_approval where beid = #{param1}")
    boolean deleteApproval(Long beid);


}
